SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO



CREATE PROCEDURE [dbo].[SP_TASALD_EMPR_Q28]  
/*-------------------------------------------------------------------------*/  
/*----- Empresa           : OFISIS S.A.                                ----*/  
/*----- Cliente           : OFISIS S.A.                                ----*/  
/*----- Sistema           : Contabilidad                               ----*/  
/*----- Modulo            : Empresa                                    ----*/  
/*----- Programa          : Reporte balance sumas y saldos             ----*/    
/*-----                     caso quiebres y filtro por cuenta          ----*/  
/*-----                     boton de filtro		               ----*/  
/*----- Nombre            : SP_TASALD_EMPR_Q28                         ----*/  
/*----- Script            : TASALD_EMPR                                ----*/  
/*----- Desarrollado por  : Percy Garc?a                               ----*/  
/*----- Fecha             : 11/02/2004                                 ----*/  
/*----- Base de Datos     : SQL 2000                                   ----*/  
/*----- Version BD        :                                            ----*/  
/*-------------------------------------------------------------------------*/  
/*-------------------------------------------------------------------------*/   
/*----- Drop proc SP_TASALD_EMPR_Q28                                   ----*/  
/*----- Grant Execute on SP_TASALD_EMPR_Q28 to PUBLIC                      ----*/  
/*----- SP_TASALD_EMPR_Q28 '01', 1, '42101', '42301', 2003, 12, 5, 2, 'S' ----*/  
/*-------------------------------------------------------------------------*/  
  
@ISCO_EMPR      TD_VC_002,   
@INNU_CNTB_EMPR TD_IN_001,  
@ISCO_CNTA_INIC TD_VC_020_CNTA,   
@ISCO_CNTA_FINA TD_VC_020_CNTA,   
@INNU_ANNO      TD_IN_001,   
@INNU_MESE      TD_IN_001,   
@INNV_CNTA      TD_IN_001,   
@INNV_QUIE_MINI TD_IN_001,   
@ISST_TODO      TD_VC_001, 
@ISCA_WHER_LOCA TD_VC_255
   
As   
Declare   
@VSCA_SELE_0005 Varchar(200),  
@VSCA_HAVI      Varchar(200),   
--@VSNV_QUIE_CNTA Varchar(6),   
@VNNU_QUIE_0001 Int,   
@VNNU_QUIE_0002 Int,    
@VNNU_QUIE_0003 Int   
  
Create Table #TWSALD_EMPR   
(CO_CNTA_EMPR Varchar(20) NULL,   
DE_CNTA_EMPR Varchar(50) NULL,   
IM_CARG_CNTB Numeric(16, 4) NULL,   
IM_ABON_CNTB Numeric(16, 4) NULL,  
IM_CARG_ACUM Numeric(16, 4) NULL,   
IM_ABON_ACUM Numeric(16, 4) NULL,   
IM_CARG_CNT2 Numeric(16, 4) NULL,   
IM_ABON_CNT2 Numeric(16, 4) NULL,  
IM_CARG_ACU2 Numeric(16, 4) NULL,   
IM_ABON_ACU2 Numeric(16, 4) NULL,   
ST_DISP_0001 Varchar(1) NULL,   
ST_DISP_0002 Varchar(1) NULL)  
  
 Select @VNNU_QUIE_0001 = Convert(Int, SubString(NV_QUIE_CNTA, 1, 2)),   
 @VNNU_QUIE_0002 = Convert(Int, SubString(NV_QUIE_CNTA, 3, 2)),   
 @VNNU_QUIE_0003 = NV_INFO_CNTA  
 From TMPARA_CONT   
 Where CO_EMPR = @ISCO_EMPR   
 And NU_ANNO = 9999  
   
 If @INNV_CNTA = @INNV_QUIE_MINI  
 Begin   
 Select @VSCA_SELE_0005 = ' 1,  '  
 End   
  
 Else  
 Begin   
 Select @VSCA_SELE_0005 = ' (Case When DataLength(TASALD_EMPR.CO_CNTA_EMPR) = Convert(Int, ' + Convert(Varchar, @INNV_CNTA)  +   
   ') Then 1 Else 0  End ),  '  
 End   
  
 If @ISST_TODO = 'S'  
  Select @VSCA_HAVI = ' '   
 Else   
  Select @VSCA_HAVI = ' Having Sum( IsNull(TASALD_EMPR.IM_CARG_CNTB, 0) ) > 0 Or Sum( IsNull(TASALD_EMPR.IM_ABON_CNTB, 0) ) > 0  
  Or Max( t4.IM_CARG_ACUM ) > 0  Or Max( t4.IM_ABON_ACUM ) > 0 '  
  
  
--- balance caso filtro por cuentas, con quiebre  
 -- , 0, 0, 0, 0, ' + @VSCA_SELE_0005 + ' 0  
Exec ('   
Insert Into #TWSALD_EMPR (CO_CNTA_EMPR, DE_CNTA_EMPR, IM_CARG_CNTB, IM_ABON_CNTB,  
  IM_CARG_ACUM, IM_ABON_ACUM, IM_CARG_CNT2, IM_ABON_CNT2,  
  IM_CARG_ACU2, IM_ABON_ACU2, ST_DISP_0001, ST_DISP_0002)  
Select TASALD_EMPR.CO_CNTA_EMPR As CUENTA_CONTABLE,  
  MAX(RTRIM(TMPLAN_EMPR.DE_CNTA_EMPR)) As DESCRIPCION_CUENTA,  
  SUM( IsNull(TASALD_EMPR.IM_CARG_CNTB, 0) ) As CARGO_PERIODO,  
  SUM( IsNull(TASALD_EMPR.IM_ABON_CNTB, 0) ) As ABONO_PERIODO,  
  Max( T4.IM_CARG_ACUM ) As CARGO_ACUMULADO,  
  Max( T4.IM_ABON_ACUM ) As ABONO_ACUMULADO, 0, 0, 0, 0, ' + @VSCA_SELE_0005 + ' 0  
 From TASALD_EMPR, TMPLAN_EMPR, (  
   Select CO_CNTA_EMPR CO_CNTA_EMPR ,  
   SUM(IsNull(IM_CARG_CNTB, 0)) IM_CARG_ACUM,  
   SUM(IsNull(IM_ABON_CNTB, 0)) IM_ABON_ACUM  
   From TASALD_EMPR
   Where CO_EMPR = "' + @ISCO_EMPR + '"  
   And NU_CNTB_EMPR = ' + @INNU_CNTB_EMPR + '  
   And NU_ANNO = ' + @INNU_ANNO + '  
   And NU_MESE <= ' + @INNU_MESE + '  
   ' + @ISCA_WHER_LOCA + ' 
   And DataLength(RTRIM(CO_CNTA_EMPR)) = ' + @INNV_CNTA + '  
   And CO_CNTA_EMPR >= "' + @ISCO_CNTA_INIC + '"  
   And CO_CNTA_EMPR <= "' + @ISCO_CNTA_FINA + '"  
   And TI_AUXI_EMPR IS NULL  
   And CO_AUXI_EMPR IS NULL  
   And CO_ORDE_SERV IS NULL  
   Group By CO_CNTA_EMPR ) T4  
 Where TASALD_EMPR.CO_EMPR = "' + @ISCO_EMPR + '"  
  And TASALD_EMPR.NU_CNTB_EMPR = ' + @INNU_CNTB_EMPR + '  
  And TASALD_EMPR.NU_ANNO = ' + @INNU_ANNO + '  
  And TASALD_EMPR.NU_MESE = ' + @INNU_MESE + ' 
  ' + @ISCA_WHER_LOCA + ' 
  And DataLength(RTRIM(TASALD_EMPR.CO_CNTA_EMPR)) = ' + @INNV_CNTA + '  
  And TASALD_EMPR.CO_CNTA_EMPR >= "' + @ISCO_CNTA_INIC + '"  
  And TASALD_EMPR.CO_CNTA_EMPR <= "' + @ISCO_CNTA_FINA + '"  
  And TASALD_EMPR.TI_AUXI_EMPR IS NULL  
  And TASALD_EMPR.CO_AUXI_EMPR IS NULL  
  And TASALD_EMPR.CO_ORDE_SERV IS NULL  
  And TMPLAN_EMPR.CO_EMPR = TASALD_EMPR.CO_EMPR  
  And TMPLAN_EMPR.NU_ANNO = 9999  
  And TMPLAN_EMPR.CO_CNTA_EMPR = TASALD_EMPR.CO_CNTA_EMPR  
  And T4.CO_CNTA_EMPR = TASALD_EMPR.CO_CNTA_EMPR  
  Group By TASALD_EMPR.CO_CNTA_EMPR  ' + @VSCA_HAVI )  
   
 -- SI ES NIVEL INFERIOR 2 ENTONCES NO HAY QUIEBRES   
 If @INNV_CNTA = @VNNU_QUIE_0001   
  Select CO_CNTA_EMPR, DE_CNTA_EMPR, IM_CARG_CNTB, IM_ABON_CNTB,  
  IM_CARG_ACUM, IM_ABON_ACUM, IM_CARG_CNT2, IM_ABON_CNT2,  
  IM_CARG_ACU2, IM_ABON_ACU2, ST_DISP_0001, ST_DISP_0002  
  From #TWSALD_EMPR  
  Order By 1  
  
 -- ENTONCES HAY QUIEBRE 3     
 If @INNV_CNTA = @VNNU_QUIE_0002  
  
  Select CO_CNTA_EMPR, DE_CNTA_EMPR, IM_CARG_CNTB, IM_ABON_CNTB,  
  IM_CARG_ACUM, IM_ABON_ACUM, IM_CARG_CNT2, IM_ABON_CNT2,  
  IM_CARG_ACU2, IM_ABON_ACU2, ST_DISP_0001, ST_DISP_0002  
  From #TWSALD_EMPR  
  Union All   
  Select SubString(t1.CO_CNTA_EMPR, 1, @VNNU_QUIE_0001), Max(t2.DE_CNTA_EMPR), 0, 0,  
  0, 0, Sum(IsNull(t1.IM_CARG_CNTB, 0)), Sum(IsNull(t1.IM_ABON_CNTB, 0)),  
  Sum(IsNull(t1.IM_CARG_ACUM, 0)), Sum(IsNull(t1.IM_ABON_ACUM, 0)), 0, 1  
  From #TWSALD_EMPR t1, TMPLAN_ANNO t2  
  Where t2.CO_CNTA_EMPR = SubString(t1.CO_CNTA_EMPR, 1, @VNNU_QUIE_0001)   
  And t2.CO_EMPR = @ISCO_EMPR   
  And t2.NU_ANNO = 9999  
  Group By SubString(t1.CO_CNTA_EMPR, 1, @VNNU_QUIE_0001)  
  Order By 1  
  
 -- ENTONCES HAY QUIEBRE 3     
 If @INNV_CNTA = @VNNU_QUIE_0003  
  
 Select CO_CNTA_EMPR, DE_CNTA_EMPR, IM_CARG_CNTB, IM_ABON_CNTB,  
 IM_CARG_ACUM, IM_ABON_ACUM, IM_CARG_CNT2, IM_ABON_CNT2,  
 IM_CARG_ACU2, IM_ABON_ACU2, ST_DISP_0001, ST_DISP_0002  
 From #TWSALD_EMPR  
 Union All   
 Select SubString(t1.CO_CNTA_EMPR, 1, @VNNU_QUIE_0001), Max(t2.DE_CNTA_EMPR), 0, 0,  
 0, 0, Sum(IsNull(t1.IM_CARG_CNTB, 0)), Sum(IsNull(t1.IM_ABON_CNTB, 0)),  
 Sum(IsNull(t1.IM_CARG_ACUM, 0)), Sum(IsNull(t1.IM_ABON_ACUM, 0)), 0, 1  
 From #TWSALD_EMPR t1, TMPLAN_ANNO t2  
 Where t2.CO_CNTA_EMPR = SubString(t1.CO_CNTA_EMPR, 1, @VNNU_QUIE_0001)   
 And t2.CO_EMPR = @ISCO_EMPR   
 And t2.NU_ANNO = 9999  
 Group By SubString(t1.CO_CNTA_EMPR, 1, @VNNU_QUIE_0001)  
 Union All   
 Select SubString(t1.CO_CNTA_EMPR, 1, @VNNU_QUIE_0002), Max(t2.DE_CNTA_EMPR), 0, 0,  
 0, 0, Sum(IsNull(t1.IM_CARG_CNTB, 0)), Sum(IsNull(t1.IM_ABON_CNTB, 0)),  
 Sum(IsNull(t1.IM_CARG_ACUM, 0)), Sum(IsNull(t1.IM_ABON_ACUM, 0)), 0, 1  
 From #TWSALD_EMPR t1, TMPLAN_ANNO t2  
 Where t2.CO_CNTA_EMPR = SubString(t1.CO_CNTA_EMPR, 1, @VNNU_QUIE_0002)   
 And t2.CO_EMPR = @ISCO_EMPR     And t2.NU_ANNO = 9999  
 Group By SubString(t1.CO_CNTA_EMPR, 1, @VNNU_QUIE_0002)   
 Order By 1  
  
/*-------------------------- FIN REPORTE --------------------------*/  
  







GO
